package com.wgy.day42.SpringJDBC;

import com.wgy.day42.数据库连接池.druid工具类.JDBCUtils;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/*
emp表中
需求: 1.修改1号数据的 salary 为10000
     2.添加一条记录
     3.删除刚添加的一条记录
     4.查询id为1的记录,将其封装为Map集合
     5.查询所有的记录,将结果封装为List集合
     6.查询所有的记录,将其封装为Emp对象的List集合
     7.查询总的记录数


*/
public class Demo02JDBCTemplate {
     //1.获取JDBCTemplate对象
    JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDataSource());

   //Junit单元测试,可以让方法独立执行

    @Test
    public void test1(){
        //2.定义SQL
        String sql="update emp set salary = 10000 where id=1001";
        //3.执行sql
        int count = template.update(sql);
        System.out.println(count);
    }

    @Test
    public void test2(){
        //2.定义sql
        String sql="insert into emp(id,name,dept_id) values(?,?,?)";
        // //3.执行sql
        int count = template.update(sql,1015,"诸葛亮",10);
        System.out.println(count);
    }

    @Test
    public void test3(){
        //2.定义sql
        String sql="delecte from emp where id =?";
        // //3.执行sql
        int count = template.update(sql,1015);
        System.out.println(count);

    }
    @Test
    public void test4(){
        //2.定义sql
        String sql="select * from emp where id =?";
        // //3.执行sql
        Map<String, Object> map = template.queryForMap(sql, 1001);
        System.out.println(map);

    }
    @Test
    public void test5(){
        //2.定义sql
        String sql="select * from emp ";
        //3.执行sql
        List<Map<String, Object>> list = template.queryForList(sql);
        for (Map<String, Object> map : list) {
            System.out.println(map);
        }

    }
    @Test
    public void test6(){
        //2.定义sql
        String sql="select * from emp ";
        //3.执行sql,并传递要封装成的对象
        List<Emp> list = template.query(sql, new RowMapper<Emp>() {
            @Override
            public Emp mapRow(ResultSet rs, int i) throws SQLException {
                Emp emp = new Emp();
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int job_id = rs.getInt("job_id");
                int mgr = rs.getInt("mgr");
                Date joinDate = rs.getDate("joinDate");
                double salary = rs.getDouble("salary");
                double bonus = rs.getDouble("bonus");
                int dept_id = rs.getInt("dept_id");
                emp.setId(id);
                emp.setName(name);
                emp.setJob_id(job_id);
                emp.setMgr(mgr);
                emp.setJoinDate(joinDate);
                emp.setSalary(salary);
                emp.setBonus(bonus);
                emp.setDept_id(dept_id);

                return emp;
            }
        });
        for (Emp emp : list) {
            System.out.println(emp);
        }

    }



    @Test
    public void test6_2(){
        //2.定义sql
        String sql="select * from emp ";
        //3.执行sql,并传递要封装成的对象
        List<Emp> list = template.query(sql,new BeanPropertyRowMapper<Emp>(Emp.class));
        for (Emp emp : list) {
            System.out.println(emp);
        }

    }
    @Test
    public void test7(){
        //2.定义sql
        String sql="select count(id) from emp ";
        // //3.执行sql
        Long total= template.queryForObject(sql, Long.class);
        System.out.println(total);

    }
}
